﻿Imports System.Data
Imports System.IO
'Imports Microsoft.Office.Interop
'Imports Microsoft.Office.Interop.Word



Partial Class Content_Administracion_Adm_controls_Clientes
    Inherits System.Web.UI.UserControl

    Dim IDataBase As New Clases.DataBase
    Dim ECConnString As String = ConfigurationManager.AppSettings("dbIP")
    Dim IControles As New Clases.controles
    Dim clid As Integer
    Dim op As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then

            clid = Request.QueryString("id")
            op = Request.QueryString("op")

            Dim edo, per, inst, Prom, AseProm, Despacho, estatus As String

            edo = "Select * from TEstados"


            estatus = "select * from TEstatus where stat_id in (1,2)"
            IControles.fill_dropDownListBaseQuery(ddl_estatus, "TEstatus", "stat_id", "stat_nombre", estatus)

            inst = "select * from vwInst where stat_id=1"
            IControles.fill_dropDownListBaseQuery(instCli, "TInstituciones", "inst_id", "inst_nombre", inst)
            IControles.fill_dropDownListBaseQuery(institucionCliB, "TInstituciones", "inst_id", "inst_nombre", inst)
            IControles.fill_dropDownListBaseQuery(instCliCta, "TInstituciones", "inst_id", "inst_nombre", inst)
            IControles.addNoneAllElement(institucionCliB)

            AseProm = "Select * from TUsuarios  where stat_id=1 and rol_id=2"
            IControles.fill_dropDownListBaseQuery(promoCli, "TUsuarios", "usr_id", "usr_nombre", AseProm)
            IControles.fill_dropDownListBaseQuery(promotorCliB, "TUsuarios", "usr_id", "usr_nombre", AseProm)
            IControles.addNoneAllElement(promoCli)
            IControles.addNoneAllElement(promotorCliB)
            Prom = "Select * from TUsuarios  where stat_id=1 and rol_id=1"
            IControles.fill_dropDownListBaseQuery(aseCli, "TUsuarios", "usr_id", "usr_nombre", Prom)
            IControles.addNoneAllElement(aseCli)

            per = "select * from vwPerf"
            IControles.fill_dropDownListBaseQuery(perCli, "TPerfiles", "per_id", "per_nombre", per)
            IControles.fill_dropDownListBaseQuery(perfilCliB, "TPerfiles", "per_id", "per_nombre", per)
            IControles.addNoneAllElement(perCli)
            IControles.addNoneAllElement(perfilCliB)

            Despacho = "select * from TDespachos"
            IControles.fill_dropDownListBaseQuery(despCli, "TDespachos", "desp_id", "desp_nombre", Despacho)
            IControles.fill_dropDownListBaseQuery(despachoCliB, "TDespachos", "desp_id", "desp_nombre", Despacho)
            IControles.addNoneAllElement(despCli)
            IControles.addNoneAllElement(despachoCliB)

            

            fechaNacCli.Attributes.Add("onKeyPress", "return false;")
            fechaNacCli.Attributes.Add("onPaste", "return false;")


            If op = "edit" Then
                clearFieldData()
                loadCostumerData(clid)
            End If


        End If
    End Sub

    Protected Sub btn_regCli_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_regCli.Click
        Dim dmlInsertCliente(), resultTransaction, InsertCliente, InsertCuenta, qryvalCliente, valCliente As String
        Dim carteraClii As Double = carteraCli.Text
        qryvalCliente = "Select Count(*) As Cliente  from TClientes where cl_contrato='" & contCli.Text & "'"
        valCliente = IDataBase.getSimpleData(qryvalCliente, "Cliente")

        If valCliente <= 0 Then

        Else
            ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Número de contrato usado previamente');", True)

        End If

        InsertCliente = "Insert into TClientes(cl_contrato,cl_nombre,cl_apellido_p,cl_apellido_m,cl_fecha_nac,cl_rfc,cl_curp,cl_calle,cl_numero,cl_colonia,cl_cp,mun_nombre,edo_nombre,pais_nombre," & _
                        "cl_telefono,cl_celular,cl_correo,per_id,cl_honorarios,inst_id,usrProm_id,usrAse_id,desp_id,cl_discrecional,cl_cartera,stat_id,cl_titular, cl_saldo_inicial, cl_saldo_final)" & _
                        "values ('" & contCli.Text & "','" & nomCli.Text & "','" & apCli.Text & "','" & amCLi.Text & "','" & fechaNacCli.Text & "'," & _
                        "'" & rfcCli.Text & "','" & curpCli.Text & "','" & calleCli.Text & "','" & txt_numero.Text & "','" & txt_colonia.Text & "','" & cpCli.Text & "','" & edocli.Text & "','" & munCli.Text & "','" & txt_pais.Text & "'," & _
                        "" & telCli.Text & "," & celCli.Text & ",'" & correoCli.Text & "','" & perCli.SelectedValue & "'," & honCli.Text & ",'" & instCli.SelectedValue & "'," & _
                        "'" & promoCli.SelectedValue & "','" & aseCli.SelectedValue & "','" & despCli.SelectedValue & "','" & RBL_disc.SelectedValue & "'," & carteraClii & "," & ddl_estatus.SelectedValue & "," & _
                        "'" & Session("ActiveUserlogin") & "',0,0)"
        IDataBase.addStringToArray(dmlInsertCliente, InsertCliente)

        InsertCuenta = "Insert into TCuentas(cl_id,cta_cuenta,cta_clabe,inst_id,stat_id,cta_titular) values (ident_current('TClientes'),'" & cuentaCl.Text & "','" & cuentaBCl.Text & "','" & instCliCta.SelectedValue & "',1,'" & Session("ActiveUserlogin") & "')"
        IDataBase.addStringToArray(dmlInsertCliente, InsertCuenta)

        Session("cta_id") = IDataBase.getSimpleData("Select ident_current('TCuentas') as cta_id from TCuentas", "cta_id")

        resultTransaction = IDataBase.execute_commandsArray(dmlInsertCliente, lb_error)

        If resultTransaction = "OK" Then
            Dim clid, ctaid As Integer
            clid = IDataBase.getSimpleData("Select ident_current('TClientes') as cliente", "cliente")
            ctaid = IDataBase.getSimpleData("Select ident_current('TCuentas') as cuenta", "cuenta")
            ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Cliente Registrado Exitosamente');window.open('Cotitulares.aspx?id=" & clid & "&ctaid=" & ctaid & "&op=1','_self');", True)
        Else
            ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Error En Transacción : " & Replace(resultTransaction, "'", "") & " \n\n Consulta Al Administrador Del Sistema');", True)
        End If

    End Sub


    Protected Sub gv_consultaClientes_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles gv_consultaClientes.RowCommand
        Dim id As Integer = e.CommandArgument

        If e.CommandName = "editarCliente" Then
            clearFieldData()
            loadCostumerData(id)
        End If


    End Sub

    Protected Sub clearFieldData()

        btn_modCli.Visible = False
        btn_regCli.Visible = True
        lnk_editaC.Visible = False
        lnk_editaB.Visible = False
        lnk_contrato.Visible = False


        carteraCli.Visible = True
        carteraCli.Enabled = True
        Label103.Visible = True
        'MaskedEditExtender1.Enabled = True
        RequiredFieldValidator19.Enabled = True
        ValidatorCalloutExtender80.Enabled = True

        contCli.Text = ""
        nomCli.Text = ""
        apCli.Text = ""
        amCLi.Text = ""
        fechaNacCli.Text = ""
        rfcCli.Text = ""
        curpCli.Text = ""
        calleCli.Text = ""
        txt_numero.Text = ""
        txt_colonia.Text = ""
        cpCli.Text = ""
        txt_pais.Text = ""
        edocli.Text = ""
        munCli.Text = ""
        telCli.Text = ""
        celCli.Text = ""
        correoCli.Text = ""
        perCli.SelectedIndex = -1
        honCli.Text = ""
        instCli.SelectedIndex = -1
        promoCli.SelectedIndex = -1
        aseCli.SelectedIndex = -1
        despCli.SelectedIndex = -1
        RBL_disc.SelectedIndex = -1
        carteraCli.Text = ""
        ddl_estatus.SelectedIndex = -1
        cuentaCl.Text = ""
        cuentaBCl.Text = ""
        instCliCta.SelectedIndex = -1
        txt_sf.Visible = False
        txt_si.Visible = False
        Label109.Visible = False
        Label110.Visible = False
        'carteraCli.Enabled = False
        Session("cl_id") = ""
        Session("cta_id") = ""
    End Sub

    Protected Sub loadCostumerData(ByVal cl_ID As Integer)

        btn_modCli.Visible = True
        btn_regCli.Visible = False
        lnk_editaC.Visible = True
        lnk_editaB.Visible = True
        lnk_contrato.Visible = True
        carteraCli.Visible = False
        Label103.Visible = False
        'MaskedEditExtender1.Enabled = False
        RequiredFieldValidator19.Enabled = False
        ValidatorCalloutExtender80.Enabled = False
        txt_sf.Visible = True
        txt_si.Visible = True
        Label109.Visible = True
        Label110.Visible = True
        Dim datoscliente As New DataTable

        Dim dmlGetCostumerData As String

        dmlGetCostumerData = "select Distinct Top 1 * from vwClientes where cl_id = '" & cl_ID & "' "
        IDataBase.createDataTable(datoscliente, dmlGetCostumerData)

        If datoscliente IsNot Nothing Then


            contCli.Text = datoscliente.Rows(0)("cl_contrato")
            nomCli.Text = datoscliente.Rows(0)("cl_nombre")
            apCli.Text = datoscliente.Rows(0)("cl_apellido_p")
            amCLi.Text = datoscliente.Rows(0)("cl_apellido_m")
            fechaNacCli.Text = datoscliente.Rows(0)("cl_fecha_nac")
            rfcCli.Text = datoscliente.Rows(0)("cl_rfc")
            curpCli.Text = datoscliente.Rows(0)("cl_curp")
            calleCli.Text = datoscliente.Rows(0)("cl_calle")
            txt_numero.Text = datoscliente.Rows(0)("cl_numero")
            txt_colonia.Text = datoscliente.Rows(0)("cl_colonia")
            cpCli.Text = datoscliente.Rows(0)("cl_cp")
            txt_pais.Text = datoscliente.Rows(0)("pais_nombre")
            edocli.Text = datoscliente.Rows(0)("edo_nombre")
            munCli.Text = datoscliente.Rows(0)("mun_nombre")
            telCli.Text = datoscliente.Rows(0)("cl_telefono")
            celCli.Text = datoscliente.Rows(0)("cl_celular")
            correoCli.Text = datoscliente.Rows(0)("cl_correo")
            txt_si.Text = Replace(datoscliente.Rows(0)("cl_saldo_inicial"), ",", ".")
            txt_sf.Text = Replace(datoscliente.Rows(0)("cl_saldo_final"), ",", ".")

            Dim per As String = "select * from vwPerf"
            IControles.fill_dropDownListBaseQuery(perCli, "TPerfiles", "per_id", "per_nombre", per)

            IControles.addNoneAllElement(perCli)
            perCli.SelectedValue = datoscliente.Rows(0)("per_id")

            honCli.Text = Replace(datoscliente.Rows(0)("cl_honorarios"), ",", ".")
            instCli.SelectedValue = datoscliente.Rows(0)("inst_id")
            promoCli.SelectedValue = datoscliente.Rows(0)("usrProm_id")
            aseCli.SelectedValue = datoscliente.Rows(0)("usrAse_id")
            despCli.SelectedValue = datoscliente.Rows(0)("desp_id")
            RBL_disc.SelectedValue = datoscliente.Rows(0)("cl_discrecional")
            carteraCli.Enabled = False
            carteraCli.Text = datoscliente.Rows(0)("cartera")
            ddl_estatus.SelectedValue = datoscliente.Rows(0)("stat_id")
            cuentaCl.Text = datoscliente.Rows(0)("cta_cuenta")
            cuentaBCl.Text = datoscliente.Rows(0)("cta_clabe")
            instCliCta.SelectedValue = datoscliente.Rows(0)("instCuenta")


        End If
        Session("cl_id") = cl_ID
        Session("cta_id") = datoscliente.Rows(0)("cta_id")

    End Sub

    Protected Sub btn_modCli_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_modCli.Click
        Dim dmlUpdateCliente() As String
        Dim UpdateCliente, UpdateCta As String
        Dim resultTransaction As String

        UpdateCliente = "UPDATE TClientes SET [cl_contrato] = '" & contCli.Text & "' ,[cl_nombre] = '" & nomCli.Text & "',[cl_apellido_p] = '" & apCli.Text & "',[cl_apellido_m] = '" & amCLi.Text & "',[cl_fecha_nac] = " & fechaNacCli.Text & "" & _
         ",[cl_rfc] = '" & rfcCli.Text & " ',[cl_curp] = '" & curpCli.Text & "',[cl_calle] = '" & calleCli.Text & "',[cl_numero] = '" & txt_numero.Text & "',[cl_colonia] = '" & txt_colonia.Text & "'" & _
         ",[cl_cp] = '" & cpCli.Text & "',[mun_nombre] = '" & munCli.Text & "',[edo_nombre] = '" & edocli.Text & "',[pais_nombre] = '" & txt_pais.Text & "',[cl_telefono] = '" & telCli.Text & "',[cl_celular] = '" & celCli.Text & "'" & _
         ",[cl_correo] = '" & correoCli.Text & " ',[per_id] = '" & perCli.SelectedValue & "',[cl_honorarios] =  '" & honCli.Text & "',[inst_id] = '" & instCli.SelectedValue & "',[usrProm_id] = '" & promoCli.SelectedValue & "'" & _
         ",[usrAse_id] = '" & aseCli.SelectedValue & " ' ,[desp_id] = '" & despCli.SelectedValue & "',[cl_discrecional] = '" & RBL_disc.SelectedValue & "',[stat_id] = '" & ddl_estatus.SelectedValue & "'" & _
         ",[cl_saldo_inicial] = '" & txt_si.Text & "', [cl_saldo_final]= '" & txt_sf.Text & "' WHERE cl_id = " & Session("cl_id") & ""
        IDataBase.addStringToArray(dmlUpdateCliente, UpdateCliente)

        UpdateCta = "UPDATE TCuentas SET [cta_cuenta] =  '" & cuentaCl.Text & "' ,[cta_clabe] = '" & cuentaBCl.Text & "',[inst_id] ='" & instCliCta.SelectedValue & "',[stat_id] = '" & ddl_estatus.SelectedValue & "' WHERE  cl_id = " & Session("cl_id") & ""
        IDataBase.addStringToArray(dmlUpdateCliente, UpdateCta)

        resultTransaction = IDataBase.execute_commandsArray(dmlUpdateCliente, lb_error)

        If resultTransaction = "OK" Then
            ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Cliente Modificado Exitosamente');window.location.href='Clientes.aspx'", True)
        Else
            ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Error En Transacción : " & Replace(resultTransaction, "'", "") & " \n\n Consulta Al Administrador Del Sistema');", True)
        End If

    End Sub

    Protected Sub lnk_editaC_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnk_editaC.Click
        Response.Redirect("Cotitulares.aspx?id=" & Session("cl_id") & "&ctaid=" & Session("cta_id") & "&op=2")
    End Sub

    Protected Sub lnk_editaB_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnk_editaB.Click
        Response.Redirect("Beneficiarios.aspx?id=" & Session("cl_id") & "&ctaid=" & Session("cta_id") & "&op=2")
    End Sub

    Protected Sub btn_canCli_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_canCli.Click
        clearFieldData()
    End Sub

    Protected Sub SearchButton_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles SearchButton.Click
        'se extraen los datos de la consulta configurada
        Dim qrySearchCl As String

        qrySearchCl = "select * from vwclientes where 1=1"

        If ContratoCliB.Text <> "" Then
            qrySearchCl = qrySearchCl & " and cl_contrato like '%" & ContratoCliB.Text & "%'"
        End If

        If NombreCliB.Text <> "" Then
            qrySearchCl = qrySearchCl & " and NombreC like '%" & NombreCliB.Text & "%'"
        End If


        If institucionCliB.SelectedValue <> "None" Then
            qrySearchCl = qrySearchCl & " and inst_id = '" & institucionCliB.SelectedValue & "' "
        End If

        If perfilCliB.SelectedValue <> "None" Then
            qrySearchCl = qrySearchCl & " and per_id = '" & perfilCliB.SelectedValue & "' "
        End If

        If promotorCliB.SelectedValue <> "None" Then
            qrySearchCl = qrySearchCl & " and usrProm_id = '" & promotorCliB.SelectedValue & "' "
        End If

        If despachoCliB.SelectedValue <> "None" Then
            qrySearchCl = qrySearchCl & " and desp_id = '" & despachoCliB.SelectedValue & "' "
        End If

      



        qrySearchCl = qrySearchCl & " order by NombreC "

        Session("qrySearchCl") = qrySearchCl

        IControles.fillGridViewPaging(gv_consultaClientes, qrySearchCl)
    End Sub


    Protected Sub gv_consultaClientes_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gv_consultaClientes.PageIndexChanging
        'se genera la paginación del gridview
        IControles.fillGridViewPaging(gv_consultaClientes, Session("qrySearchCl"), Convert.ToInt32(e.NewPageIndex))
    End Sub

    Protected Sub lnk_contrato_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnk_contrato.Click
        Dim clid, ctaid As Integer
        clid = IDataBase.getSimpleData("Select ident_current('TClientes') as cliente", "cliente")
        ctaid = IDataBase.getSimpleData("Select ident_current('TCuentas') as cuenta", "cuenta")
        Response.Redirect("Contrato.aspx?id=" & clid & "&ctaid=" & ctaid & "&op=2")
    End Sub
End Class
      
